﻿Public Class WorkerList

  Private UserStatus As New Dictionary(Of Integer, String)

  Private SqlStrWorkerList As String
  Private SqlFilter As String
  Private SqlFastSearch As String

  'массив полей фильтра
  Private FieldsFilter As ArrayList = New ArrayList()

  'массив условий фильтра
  Private SelFilters As ArrayList = New ArrayList()

  Private Sub FormList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Call InitForm()
    Call InitFieldsFilter()

  End Sub


  Private Sub InitForm()

    'перенаправляем событие с DGV 
    AddHandler DGV.KeyDown, AddressOf DGVKeyDown
    AddHandler DGV.CellPainting, AddressOf DGV_CellPainting

    'события поисковой строки
    AddHandler StrSearch.Enter, AddressOf StrFind_Enter
    AddHandler StrSearch.Leave, AddressOf StrFind_Leave
    StrSearch.Text = Params.StrSearchLabel


    Dim colIcon As New DataGridViewImageColumn
    colIcon.HeaderText = ""
    colIcon.Name = "Icon"
    colIcon.Width = 20
    colIcon.Resizable = DataGridViewTriState.False
    DGV.Columns.Add(colIcon)

    Dim colTextBox As New DataGridViewTextBoxColumn
    colTextBox.HeaderText = "Фамилия, имя, отчество"
    colTextBox.Name = "FIO"
    colTextBox.MinimumWidth = 100
    colTextBox.Width = 250
    DGV.Columns.Add(colTextBox)

    colTextBox = New DataGridViewTextBoxColumn
    colTextBox.HeaderText = "Должность"
    colTextBox.Name = "Post"
    colTextBox.MinimumWidth = 100
    colTextBox.Width = 150
    DGV.Columns.Add(colTextBox)

    colTextBox = New DataGridViewTextBoxColumn
    colTextBox.HeaderText = "Отдел"
    colTextBox.Name = "Otdel"
    colTextBox.MinimumWidth = 100
    colTextBox.Width = 250
    DGV.Columns.Add(colTextBox)

    colTextBox = New DataGridViewTextBoxColumn
    colTextBox.HeaderText = "Статус"
    colTextBox.Name = "Status"
    colTextBox.MinimumWidth = 75
    DGV.Columns.Add(colTextBox)

    UserStatus.Add(0, "Работает")
    UserStatus.Add(1, "Уволен")
    UserStatus.Add(2, "Командировка")
    UserStatus.Add(3, "Отпуск")
    UserStatus.Add(4, "Больничный")
    UserStatus.Add(5, "Декрет")
    UserStatus.Add(6, "Прочее")

    'главный SQL запрос для этой формы
    SqlStrWorkerList = "SELECT Workers.ID_Worker, Workers.F_Worker, Workers.N_Worker, Workers.P_Worker, Workers.I_Worker, Workers.ID_Post, "
    SqlStrWorkerList += " Workers.ID_Otdel, Workers.Login, Workers.Fl_Rel, Workers.GALID, Workers.PersonIndexDP, Posts.N_Post,"
    SqlStrWorkerList += " Otdels.Name_Otdel "
    SqlStrWorkerList += " FROM Workers INNER JOIN"
    SqlStrWorkerList += " Posts ON Workers.ID_Post = Posts.ID_Post INNER JOIN"
    SqlStrWorkerList += " Otdels ON Workers.ID_Otdel = Otdels.ID_Otdel"
    'SqlStrWorkerList += " ORDER BY  Workers.Fl_Rel, Workers.F_Worker"


    'Добавление столбцов

    'Dim colCheckBox As New DataGridViewCheckBoxColumn
    'colCheckBox.HeaderText = ""
    'colCheckBox.Name = "ColCheckBox"
    'colCheckBox.Width = 20
    'colCheckBox.Resizable = DataGridViewTriState.False
    'DGV.Columns.Add(colCheckBox)

    'Dim colIcon As New DataGridViewImageColumn
    'colIcon.HeaderText = ""
    'colIcon.Name = "ColImage"
    'colIcon.Width = 20
    'colIcon.Resizable = DataGridViewTriState.False
    'DGV.Columns.Add(colIcon)

    'Dim colTextBox As New DataGridViewTextBoxColumn
    'colTextBox.HeaderText = "ColTextBox1"
    'colTextBox.Name = "ColTextBox1"
    'colTextBox.MinimumWidth = 50
    'DGV.Columns.Add(colTextBox)



    'For i = 1 To 100
    '  DGV.Rows.Add()
    'Next



  End Sub


  ''' <summary>
  ''' Настройка полей фильтра
  ''' </summary>
  ''' 
  Private Sub InitFieldsFilter()

    'настройка полей фильтра:
    Dim FClass As Dictionary(Of String, Object)

    FClass = New Dictionary(Of String, Object)
    FClass.Add("Title", "ФИО")
    FClass.Add("Name", "FIO")
    FClass.Add("Type", "string")
    FClass.Add("Value", "")
    FieldsFilter.Add(FClass)

    FClass = New Dictionary(Of String, Object)
    FClass.Add("Title", "Должность")
    FClass.Add("Name", "Post")
    FClass.Add("Type", "string")
    FClass.Add("Value", "")
    FieldsFilter.Add(FClass)

    FClass = New Dictionary(Of String, Object)
    FClass.Add("Title", "Отдел")
    FClass.Add("Name", "NameOtdel")
    FClass.Add("Type", "string")
    FClass.Add("Value", "")
    FieldsFilter.Add(FClass)

    FClass = New Dictionary(Of String, Object)
    FClass.Add("Title", "Статус")
    FClass.Add("Name", "Status")
    FClass.Add("Type", "combobox")
    FClass.Add("Value", UserStatus)   'массив
    FieldsFilter.Add(FClass)

  End Sub

  ''' <summary>
  ''' Загрузка списка сотрудников
  ''' </summary>
  ''' <remarks></remarks>
  Private Sub LoadListWorkers()

    DGV.Rows.Clear()

    'фильтр добавлять в основной запрос, быстрый поиск в обертке....
    'фильтр + быстрый поиск...
    Dim DRC As DataRowCollection = DB.GetFields(SqlStrWorkerList + SqlFilter + SqlFastSearch)

    If DRC Is Nothing = False Then

      Call InitPB(PB, DRC.Count)

      For Each dr As DataRow In DRC

        Call AddRowInDGV(dr)

        PB.Value += 1
        If DGV.Rows.Count Mod 2 = 0 Then
          Application.DoEvents()
        End If

      Next

    End If

    PB.Visible = False

    CountRowsDGBLabel.Text = "Строк в таблице: " + String.Format("{0:N0}", DGV.Rows.Count)

  End Sub

  ''' <summary>
  ''' Добавление строки с данными в DGV
  ''' </summary>
  ''' <param name="dr"></param>
  ''' <remarks></remarks>
  Private Sub AddRowInDGV(ByVal dr As DataRow, Optional ByVal IndexRowDGV As Integer = -1)

    Dim DGVRow As DataGridViewRow

    If IndexRowDGV = -1 Then
      DGV.Rows.Add()
      DGVRow = DGV.Rows(DGV.Rows.GetLastRow(DataGridViewElementStates.Visible))
    Else
      DGVRow = DGV.Rows(IndexRowDGV)
    End If

    DGVRow.Tag = dr
    DGVRow.Cells("FIO").Value = dr("F_Worker") + " " + dr("N_Worker") + " " + dr("P_Worker")
    DGVRow.Cells("Post").Value = dr("N_Post")
    DGVRow.Cells("Otdel").Value = dr("Name_Otdel")
    DGVRow.Cells("Status").Value = UserStatus(Val(dr("Fl_Rel")))

    'иконка
    If Val(dr("Fl_Rel")) = 0 Then
      DGVRow.Cells("Icon").Value = My.Resources.user
    Else
      DGVRow.Cells("Icon").Value = My.Resources.user_not_active
    End If


  End Sub


  ''' <summary>
  ''' Обновление записи в таблице по указанному условию
  ''' </summary>
  ''' <param name="WhereValue">Условия для выбора записи</param>
  ''' <remarks></remarks>
  Private Sub updateDataRow(ByVal WhereValue As String, ByVal IndexRowDGV As Integer)

    If WhereValue = "" Then Exit Sub
    WhereValue = String.Format(" WHERE {0}", WhereValue)

    Dim DRC As DataRowCollection = DB.GetFields(SqlStrWorkerList + WhereValue)

    If DRC Is Nothing = False Then

      Call AddRowInDGV(DRC(0), IndexRowDGV)

    End If


  End Sub


#Region "Быстрый поиск"

  Private Sub FastSearch(ByVal s As String)

    s = DB.SetQuotes(s.Trim().ToLower())

    Dim flFilter As Boolean

    If s.Length < 3 Then

      If SqlFastSearch <> "" Then
        SqlFastSearch = ""
        flFilter = True
      End If

    Else

      If SqlFilter = "" Then
        SqlFastSearch = " WHERE "
      Else
        SqlFastSearch = " AND "
      End If

      SqlFastSearch += " (CHARINDEX('" + s + "', N_Post)<>0"
      SqlFastSearch += " OR CHARINDEX('" + s + "', Name_Otdel)<>0"
      SqlFastSearch += " OR CHARINDEX('" + s + "', F_Worker + ' ' + N_Worker + ' ' + P_Worker)<>0)"

      flFilter = True

    End If

    If flFilter Then
      Call LoadListWorkers()
    End If

  End Sub

  Private Sub StrSearch_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles StrSearch.KeyDown

    FastSearchTimer.Enabled = False

  End Sub


  Private Sub StrSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles StrSearch.TextChanged

    If StrSearchLabel = StrSearch.Text Or StrSearch.Tag = StrSearch.Text Then Exit Sub

    StrSearch.Tag = StrSearch.Text
    FastSearchTimer.Enabled = True

  End Sub


  Private Sub FastSearchTimer_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FastSearchTimer.Tick

    FastSearchTimer.Enabled = False
    Call FastSearch(StrSearch.Text)

  End Sub

#End Region


  ''' <summary>
  ''' Установка фильтра
  ''' </summary>
  ''' <remarks>Разбор массива установленных фильтров и формирование строки SQL - SqlFilter </remarks>
  Private Sub SetFilter()

    SqlFilter = ""

    If SelFilters.Count > 0 Then

      SqlFilter += " WHERE ("

      For Each Fitem As Dictionary(Of String, String) In SelFilters

        'оператор
        If Fitem("Oper") = "И" Then
          SqlFilter += " AND "
        ElseIf Fitem("Oper") = "ИЛИ" Then
          SqlFilter += " OR "
        End If

        'ДЛЯ НАСТРОЙКИ ФИЛЬТРА - ПРАВИТЬ ТУТ: vvvvvvvvvvvv

        If Fitem("NameField") = "FIO" Then 'тип string

          If Fitem("Condition") = "содержит" Then
            SqlFilter += String.Format(" CHARINDEX('{0}', F_Worker + ' ' + N_Worker + ' ' + P_Worker) <> 0", Fitem("Value"))
          ElseIf Fitem("Condition") = "не содержит" Then
            SqlFilter += String.Format(" CHARINDEX('{0}', F_Worker + ' ' + N_Worker + ' ' + P_Worker) = 0", Fitem("Value"))
          ElseIf Fitem("Condition") = "равно" Then
            SqlFilter += String.Format(" F_Worker + ' ' + N_Worker + ' ' + P_Worker = '{0}'", Fitem("Value"))
          ElseIf Fitem("Condition") = "не равно" Then
            SqlFilter += String.Format(" F_Worker + ' ' + N_Worker + ' ' + P_Worker <> '{0}'", Fitem("Value"))
          End If

        ElseIf Fitem("NameField") = "Post" Then 'тип string

          If Fitem("Condition") = "содержит" Then
            SqlFilter += String.Format(" CHARINDEX('{0}', N_Post) <> 0", Fitem("Value"))
          ElseIf Fitem("Condition") = "не содержит" Then
            SqlFilter += String.Format(" CHARINDEX('{0}', N_Post) = 0", Fitem("Value"))
          ElseIf Fitem("Condition") = "равно" Then
            SqlFilter += String.Format(" N_Post = '{0}'", Fitem("Value"))
          ElseIf Fitem("Condition") = "не равно" Then
            SqlFilter += String.Format(" N_Post <> '{0}'", Fitem("Value"))
          End If

        ElseIf Fitem("NameField") = "NameOtdel" Then 'тип string

          If Fitem("Condition") = "содержит" Then
            SqlFilter += String.Format(" CHARINDEX('{0}', Name_Otdel) <> 0", Fitem("Value"))
          ElseIf Fitem("Condition") = "не содержит" Then
            SqlFilter += String.Format(" CHARINDEX('{0}', Name_Otdel) = 0", Fitem("Value"))
          ElseIf Fitem("Condition") = "равно" Then
            SqlFilter += String.Format(" Name_Otdel = '{0}'", Fitem("Value"))
          ElseIf Fitem("Condition") = "не равно" Then
            SqlFilter += String.Format(" Name_Otdel <> '{0}'", Fitem("Value"))
          End If

        ElseIf Fitem("NameField") = "Status" Then 'тип ComboBox

          If Fitem("Condition") = "равно" Then
            SqlFilter += String.Format(" Fl_Rel = {0}", Fitem("Value"))
          ElseIf Fitem("Condition") = "не равно" Then
            SqlFilter += String.Format(" Fl_Rel <> {0}", Fitem("Value"))
          End If

        End If

        'СПРАВКА ПО ТИПАМ УСЛОВИЙ ФИЛЬТРА:
        'string:
        '-содержит
        '-не содержит
        '-равно
        '-не равно
        '======================================
        'combobox:
        '-равно
        '-не равно
        '======================================
        'date:
        '-равно
        '-не равно
        '-больше или равно
        '-меньше или равно
        '======================================

      Next

      SqlFilter += ")"

      cmdSelFilter.BackColor = Color.PaleGreen

    Else

      cmdSelFilter.BackColor = Panel1.BackColor

    End If

  End Sub



  ''' <summary>
  ''' Открытие карточки сотрудника
  ''' </summary>
  ''' <remarks></remarks>
  Private Sub OpenWorkerCard()

    If DGV.SelectedRows.Count = 0 Then Exit Sub

    Dim WC As New WorkerCard
    WC.IDW = DirectCast(DGV.SelectedRows(0).Tag, DataRow)("ID_Worker").ToString()
    WC.ShowDialog()

    If WC.flSave Then
      updateDataRow(String.Format(" Workers.ID_Worker = {0} ", WC.IDW), DGV.SelectedRows(0).Index)
    End If

    WC.Dispose()

  End Sub



  ''СОБЫТИЯ:

  Private Sub FormList_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown

    If e.KeyCode = Keys.Escape Then Me.Close()

  End Sub

  Private Sub FormList_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown

    Call LoadListWorkers()

  End Sub

  Private Sub cmdSelFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelFilter.Click

    Dim fil As New Filter
    fil.FieldsFilter = FieldsFilter
    fil.SelFilters = SelFilters
    fil.ShowDialog()

    If fil.flSetFilter Then

      SelFilters = fil.SelFilters
      Call SetFilter()

      Call LoadListWorkers()

    End If

    fil.Dispose()

  End Sub

  Private Sub DGV_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV.CellDoubleClick

    If e.RowIndex = -1 Then Exit Sub

    Call OpenWorkerCard()

  End Sub

  Private Sub МенюСотрудникиДобавить_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles МенюСотрудникиДобавить.Click

    Dim WC As New WorkerCard
    WC.ShowDialog()

  End Sub

  Private Sub МенюСотрудникиИзменить_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles МенюСотрудникиИзменить.Click

    Call OpenWorkerCard()

  End Sub

  Private Sub DGV_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DGV.KeyDown

    If e.KeyCode = Keys.Return Then Call OpenWorkerCard()

  End Sub

End Class
